package com.xdjmall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.xdjmall.dao.CarouselDao;
import com.xdjmall.entity.Carousel;
import com.xdjmall.util.DBUtils;
import com.xdjmall.util.QueryCondition;
import com.xdjmall.util.SQLHandler;

public class CarouselDaoImpl implements CarouselDao {
	@Override
	public Integer insert(Carousel t) throws SQLException {
		String sql = "insert into carousel(carousel_url,redirect_url,carousel_rank,create_user,update_user,carousel_id) "
				+ " values(?,?,?,?,?,?)";
		try (Connection c = DBUtils.getConnection(); 
			PreparedStatement ps = c.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);) {
			ps.setString(1, t.getCarouselUrl());
			ps.setString(2, t.getRedirectUrl());
			ps.setInt(3, t.getCarouselRank());
			ps.setInt(4, t.getCreateUser());
			ps.setInt(5, t.getUpdateUser());
			ps.setInt(6, t.getCarouselId());
			int row = ps.executeUpdate();
			//得到返回的生成
			ResultSet keySet = ps.getGeneratedKeys();
			if (keySet.next()) {
				t.setCarouselId(keySet.getInt(1));
			}
			return row;
		}
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		// 不实现
		return null;
	}

	@Override
	public Integer update(Carousel t) throws SQLException {
		String sql = "update carousel set ";
		if (t.getCarouselUrl() != null) {
			sql += " carousel_url=?,";
		}
		if (t.getRedirectUrl() != null) {
			sql += " redirect_url=?,";
		}
		if (t.getCarouselRank() != null) {
			sql += " carousel_rank=?,";
		}
		if (t.getCreateUser() != null) {
			sql += " create_user=?,";
		}
		if (t.getUpdateUser() != null) {
			sql += " update_user=?,";
		}
		// 截取并删除逗号 substring 包前不包后
		sql += " update_time=now() where carousel_id=? and is_delete=0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int num = 1;
			if (t.getCarouselUrl() != null) {
				ps.setString(num++, t.getCarouselUrl());
			}
			if (t.getRedirectUrl() != null) {
				ps.setString(num++, t.getRedirectUrl());
			}
			if (t.getCarouselRank() != null) {
				ps.setInt(num++, t.getCarouselRank());
			}
			if (t.getCreateUser() != null) {
				ps.setInt(num++, t.getCreateUser());
			}
			if (t.getUpdateUser() != null) {
				ps.setInt(num++, t.getUpdateUser());
			}
			ps.setInt(num, t.getCarouselId());
			return ps.executeUpdate();
		}
	}

	@Override
	public Carousel selectById(Integer id) throws SQLException {
		// TODO 高晓宇
		String sql = "select * from carousel where carousel_id =? and is_deleted = 0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Carousel car = new Carousel();
				car.setCarouselId(rs.getInt(1));
				car.setCarouselUrl(rs.getString(2));
				car.setRedirectUrl(rs.getString(3));
				car.setCarouselRank(rs.getInt(4));
				car.setIsDeleted(rs.getBoolean(5));
				car.setCreateTime(rs.getDate(6).toLocalDate());
				car.setCreateUser(rs.getInt(7));
				car.setUpdateTime(rs.getDate(8).toLocalDate());
				car.setUpdateUser(rs.getInt(9));
				return car;
			}
		}
		return null;
	}

	@Override
	public Integer selectCount() throws SQLException {
		// TODO 高晓宇
		String sql = "select count(*) from carousel where is_deleted = 0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		}
		return null;
	}

	@Override
	public List<Carousel> selectByPage(QueryCondition condition) throws SQLException {
		// TODO 杨天保
		List<Carousel> list = new ArrayList<>();
		String sql = "select * from carousel where is_deleted=0 order by carousel_rank desc ";
		Integer page = condition.getPage();
		Integer pageSize = condition.getPageSize(); // 分页条件
		if (page != null && pageSize != null) {
			sql += " limit ?,? ";
		}
		int num = 1;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			if (page != null && pageSize != null) {
				ps.setInt(num, (page - 1) * pageSize);
				num++;
				ps.setInt(num, pageSize);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Carousel car = new Carousel();
				car.setCarouselId(rs.getInt(1));
				car.setCarouselUrl(rs.getString(2));
				car.setRedirectUrl(rs.getString(3));
				car.setCarouselRank(rs.getInt(4));
				car.setIsDeleted(rs.getBoolean(5));
				car.setCreateTime(rs.getDate(6).toLocalDate());
				car.setCreateUser(rs.getInt(7));
				car.setUpdateTime(rs.getDate(8).toLocalDate());
				car.setUpdateUser(rs.getInt(9));
				list.add(car);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Override
	public Integer deleteBatch(Integer[] ids) throws SQLException {
		// TODO 杨天保
		String sql = "update carousel set update_time=now(), is_deleted=1 " + " where is_deleted=0 and carousel_id "
				+ SQLHandler.joinIn(ids);
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int row = ps.executeUpdate();
			return row;
		}
	}
	
	@Override
	public List<Carousel> selectCarouselsByNum(Integer number) throws SQLException {
		// TODO 杨天保
		List<Carousel> list = new ArrayList<>();
		String sql = "select * from carousel "
				+ " where is_deleted=0 order by carousel_rank desc limit ?";
		int num = 1;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Carousel car = new Carousel();
				car.setCarouselId(rs.getInt(1));
				car.setCarouselUrl(rs.getString(2));
				car.setRedirectUrl(rs.getString(3));
				car.setCarouselRank(rs.getInt(4));
				car.setIsDeleted(rs.getBoolean(5));
				car.setCreateTime(rs.getDate(6).toLocalDate());
				car.setCreateUser(rs.getInt(7));
				car.setUpdateTime(rs.getDate(8).toLocalDate());
				car.setUpdateUser(rs.getInt(9));
				list.add(car);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}
}
